Hello and welcome to another
Postgres walkthrough. In this walkthrough, we're
going to walk through the stuff we're doing
in the second lecture. This is a lecture about
database design, and foreign keys, and
various relations. So the one thing we're doing in this lecture is something
we're going to automate much later and that is building database tables that are linked together and database
rows that are linked to other rows by hand
just so you can see them. Just once we automate them, I don't want you to
forget how they work. So we're going to have
a bunch of tables. And the data model here is there's a track table that has a foreign key to an album table. The album table has a foreign
key to the artist table, and then the track also has
a link to the genre table. I call these lookup tables, they have an id which is
automatically assigned. We put a name in,
we call it UNIQUE, just because if we're going
to call a Led Zeppelin, we only want one Led
Zeppelin row because ultimately we're going make
Led Zeppelin be a number. So you see this one here, the album table
has a foreign key. And so the thing is these foreign key columns
are just integer numbers. The id SERIAL is a
little special but it's just an integer number that's
automatically generated. And so after we've come
up with a data model, designed the picture, then we
can create all these tables. Now, this is all
set up so that you can mostly just cut and paste
things really fast into, I've logged into my
Postgres and off I go. We can go as fast as I
want with these things. I can just grab a whole bunch and because they
end in semicolons, and it's somehow
the Postgres client keeps up with the
pasting and it all works pretty well and so there you go. I've created an artist, album, genre, and track table. And so now I'm going to
do some inserting. So the key thing is if I look like INSERT INTO artist
Led Zeppelin here, and then I SELECT
star FROM artist, I hit the tab button there, select star from artist,
then you see and the id is the key thing. and I'll put another artist, AC/DC. So these are the lookup tables, these are the tables
that we are filling in with the string value
so that we can create numbers that we can use
everywhere else in the system. Now the foreign keys like
artist_id, we have to explicitly. We'll come up with other
ways later where the sub-selects, etc., can look these
up, but in the short term, we're just going to do
this manually and so that means that we need to
remember that Who Made Who, which is AC/DC, is number 2. So forever in this system now, AC/DC is going to
be the number 2. So now we can make an
album and indicate that it's artist number 2
with this INSERT statement, Who Made Who and number 2, okay? And so we can do the same thing with Led Zeppelin's album 4. There you go. And we'll
do the same thing, genre is another lookup table. So we'll just insert
these two things. Make sure you do
these things in order otherwise my numbers
don't work anymore, SELECT star FROM genre. So we can see what the
genre numbers are. Now if I was doing this, I
might have to write this down on a piece of paper
but I already did it. So I've got these
numbers just right. So this next INSERT INTO track, this looks very complex
but it's just got title, length, rating, count,
album_id, genre_id, and this 2 and this 1
at the very back end here, those are the numbers that
are the foreign keys. So you can't really sort of
tell the difference between count, which is just an integer,
and album_id and genre_id. They're just integers in this particular INSERT statement. And so the database knows because of the fact
that we use this, it's a reference and it's
a foreign key and we've communicated on the
CREATE statement what these really mean, and so SQL can optimize them. But in the INSERT statement, they're just numbers
and so we can put all those things in and we now, Oh, I forgot to copy and paste. See? Oh, this is really cool, INSERT INTO genre name, VALUES rock, duplicate key
violates unique constraint. This keeps you from inserting it. I didn't cut and paste quite right and so I'd tried
to do that twice. But if you look in track, I'd tried to reinsert
the genre but because I have this VARCHAR 128 UNIQUE, the fact that I
already had Rock in there, my database complained. Now this is not a bug. This is me in my database
CREATE statement protecting me as typing in SQL commands from doing something stupid
and making a mistake, right? And so it saved me from myself. So let me copy and paste
this correctly and get that record inserted into
track, INSERT INTO track, and we go SELECT star FROM track and see those numbers and even in the SELECT the
album_id and the genre_id, just look like normal integers but inside the database, trust me, Postgres is thinking this
through very carefully. So let's insert the rest of these things and get them all in. So now we have four
records, SELECT COUNT star FROM track, semicolon. So we have four rows
in there, four rows. So this is our first join, we are going to read
the album title and the artist's name from the
album joined with the artist, on the condition that the album's artist_id is
equal to the artist.id. So let's take a look at that. There we go, right? We've made who and so that's going
across two tables. It might be easier to see
this if we show album id, artist id and album id
in the query. So this next query does that. So now we can see
how the artist id, the rows, are the rows that match between artist
id and album id, and those are the
rows we're seeing. But what the SELECT does, this album.artist_id
and artist.id don't really need to be shown
for us as the developer. I'm just showing it so
that we can kind of understand the database mechanisms
that are going on. Now, you can kind of see
something about how joins work if we run a CROSS
JOIN between those. Between two things. A CROSS JOIN doesn't filter out the
things that don't match. The CROSS JOIN shows
all the combinations of all the tracks with all the combinations
of all the genres. And then, you'll notice that
sometimes these numbers match between genre_id
and genre.id. Sometimes they don't match. And so really, a regular JOIN is a CROSS JOIN with a filter
that requires the match. And so that's where the
ON clause comes in. The ON clause says,
do a CROSS JOIN, but throw away the things that
don't match the ON clause. So we can see that. So we'll turn this into
a normal INNER JOIN or non-CROSS JOIN
with an ON clause, and we see that it filters out only the things that we want
where the rows really match. There's one there, there's one there. About to Rock matches, Who Made Who matches. And then, Stairway is not Metal
and Black Dog is not Metal. And the CROSS JOIN shows
all those combinations, but the ON clause of the regular JOIN filters out so that we only
see the matches. And after a while, we
just stop thinking about this to realize that this is a CROSS
JOIN with a filter. Regular old JOIN is a CROSS
JOIN without a filter. And if you want to do the
whole big SELECT where we join across all four tables, it's not all that hard. You got a JOIN and
you got a ON clause. You got another JOIN,
you got a ON clause, and the ON clause looks the same. We named these artist_id, and the primary keys are id, and the foreign keys
have the name of the table. This convention greatly helps when you're typing JOIN commands. Okay? And so that's the many-to-one
relationship. That's four tables and three
many-to-one relationships. Just to show you how the
ON DELETE CASCADE works, let me do a SELECT
star FROM tracks. And you see that I have
four records there. That's not what I wanted to do. SELECT star FROM track. And then, if I delete genre, and what we're demonstrating
here is the fact that the track table says
ON DELETE of a genre row, delete the corresponding track. And so you'll see that
even though I'm just deleting
something from genre, so SELECT star FROM genre, you see there's only one left, but the real effect is that
we can do a SELECT star FROM track and those two went away. So the genre_id that
had 2's are gone now. So that's because the DELETE from the genre table cascaded
into the track table. So that's pretty cool.
Okay. So now let's switch to many-to-many. But the key to many-to-many
is not all that crazy. The many-to-many is a two
one-to-many relationships. So if we take a look at this many-to-many relationship
between students, courses, and members, the student
is just a lookup table, right? You see that it's
got an id, a name, an email as VARCHAR UNIQUE, and course table that's got a
title, it's VARCHAR UNIQUE. Again, that VARCHAR unique
keeps us from making mistakes and putting
duplicate things in. So let's create those two tables. Those two tables are
the basic tables. But then we have here. Some people want to put an
id SERIAL in the table, but I'm going to make it
so that the primary key in this table is a combination of a student_id and a course_id. So this is the join table or the junction table or whatever. student_id is a foreign
key into student, course_id is a foreign
key into course. And these look exactly
like foreign keys because literally many-to-many is this middle table
with two left and right outbound foreign keys. And then primary key, you can
actually put a combination. So it's the combination. So 1, 2 is okay. student_id of 1, course_id of 2 is okay. student_id of 2, 2 is okay, but you can't have
two rows that have identical combination of
student_id and course_id. So that's the primary key. You want that because you
really just want to say, well, this student is only allowed to be in this course one time, we don't need extra rows. There might be times when
you don't care about that, when you can have more than one perhaps in particular
because we're modeling this role value at the connection between
a student and a course. Maybe we've decided
that we can have a student and course
with a different role. Then, I would make my primary key be student_id, course_id, role. Then, there are
those three things that have to be unique. So you could be in as a
student and an instructor. So that's just the data model. So we have lookup tables, the student and the
course lookup tables, and I'll just grab
those INSERT statements and send them in. That's what's fun about this. So now I've just got SELECT. I've got three records in each of the student
and course tables. SELECT star FROM student, SELECT star FROM course. You see three records, but really what
we've also done is created the id fields
for these things. And now we're going to start adding the connections between them. And so we have to know these numbers. Again, now I've got
INSERT INTO member, a whole series of
INSERT INTO members. Now, this was hard. And I had to go back and get
all the numbers right. But, again, you see student_id, course_id are just integers. Like the first one is 1, 1. That means Jane is going into the Python course and she is
going in as the instructor. Now 2, 1 means that Ed is going
into the Python course, and Ed is going in with
a 0 as the student. And so I'm just going to blast
all these things in there. I'm going to send these, looks like seven
INSERT statements with all the foreign keys. And in they go. And now I can say SELECT
star FROM member. I do that. I make that
typo all the time. From, not form.
And they're just numbers, right? But these are links. Now, the role is
not a foreign key, but course_id and
student_id are foreign keys. When you construct a JOIN, I think of it as joining
through the member table. Now, it turns out you can write this SELECT statement
here with student.name, member.role, course.title. The from and the member, you can put them in
different orders, but I like to draw them
in a particular order, and particularly I like the
join table in the middle, right? So I'm going from
the student table, joining through the member
table into the course table. But they're really just joined up and Postgres figures
out all that stuff. Of course, I got to ORDER BY, I've got the course title, member role descending, and the student name just because
I want it to look pretty, because I want to see
everybody next to each other in the course. So we can see all these folks. We can see Ed is the teacher of PHP and Sue is the student. Jane is the teacher of Python and Ed and Sue
are students, etc. So that gets you through
some many-to-many stuff. Now, this is the manual way of inserting these foreign keys with these hard-coded numbers. You need to write these numbers down on a little piece of paper. And I'm not going to make
you do too much of this by hand because
the next thing we're going to teach you is how to automate all this stuff. Cheers.